﻿//  Summary:   Provides replacement functionality for the undocumented
//             system stored procedures: sp_MSForEachDB and sp_MSForEachTable
//             
//  Date:	   March 1, 2011
//  Author:    boB Taylor
//             MCA, MCM, MCT, MCITP, MCSD.NET, MCSE

//---------------------------------------------------------------------
//  Copyright (C) Microsoft Corporation.  All rights reserved.

//  This source code is intended only as a supplement to Microsoft
//  Development Tools and/or on-line documentation.  See these other
//  materials for detailed information regarding Microsoft code samples.

//  THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
//  KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
//  IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A
//  PARTICULAR PURPOSE.


using System;
using System.Collections;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Globalization;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;
using SqlIterators.Properties;

namespace SqlIterators
{
    /// <summary>
    /// This enum specifies how much feedback to give to the user
    /// </summary>
    public enum MessageLevels
    {
        /// <summary>
        /// Only return errors
        /// </summary>
        Errors,
        /// <summary>
        /// Errors, and progress messages
        /// </summary>
        Progress,
         /// <summary>
        /// Very msgLevel
        /// </summary>
        All,
        /// <summary>
        /// If the user passed an invalid option
        /// </summary>
        Invalid
    };
    /// <summary>
    /// This <c>SqlIterators</c>namespace encloses the SQL CLR stored procedures that are used to emulate the undocumnted system stored procedures
    /// <list type="bullet">
    /// <item>
    /// <description>sp_MSForEachTable</description>
    /// </item>
    /// <item>
    /// <description>sp_MSForEachDB</description>
    /// </item>
    /// <item>
    /// <description>sp_MSForEachWorker</description>
    /// </item>
    /// </list>
    /// </summary>
    [System.Runtime.CompilerServices.CompilerGeneratedAttribute()]
    class NamespaceDoc
    {
    };
    /// <summary>
    /// This class implements the functionality contained in system stored procedures: 
    /// sp_MSForEachDB and sp_MSForEachTable
    /// </summary>
    public static partial class MSForEachReplacements
    {
        /// <summary>
        /// This is the entry point for the SQL CLR stored procedure ExecuteForEachDB
        /// </summary>
        /// <param name="command1">This is the command to be iterated over for each database.
        /// calling with an empty command will present the user with detailed usage help
        /// </param>
        /// <param name="replaceChar">If the user cannot use the ? as the replacement character they may specific one here
        /// </param>
        /// <param name="command2">This is the command to be iterated over for each database.
        /// this command will be issued after iterating through all databases for the first command
        /// </param>
        /// <param name="command3">This is the command to be iterated over for each database.
        /// this command will be issued after iterating through all databases for the first two command
        /// </param>        
        /// <param name="precommand">A statement to run once at the begining of the batch
        /// </param>
        /// <param name="postcommand">A statement to run once at the end of the batch</param>
        /// <param name="setNoCountOn">
        /// An option to turn NOCOUNT on or off
        /// </param>
        /// <param name="messageOptions">Determines the level of feedback to the user during execution
        /// </param>
        /// <returns>zero if successful, non-zero if unsuccessful</returns>
       [Microsoft.SqlServer.Server.SqlProcedure(Name = "ExecuteForEachDB")]
        public static SqlInt32 ExecuteForEachDB(
             string command1
           , string replaceChar
           , string command2
           , string command3
           , string precommand
           , string postcommand
           , bool setNoCountOn = true
           , string messageOptions = "Errors"
           )
        {
           int retValue = 0;
           MessageLevels msgLevel = MessageLevel(messageOptions);
           if(!(msgLevel == MessageLevels.Invalid))
           {
               using (SqlConnection connection = new SqlConnection(Resources.contextConnectionString))
               {
                   connection.Open();
                   if(ValidateParameters(command1,Resources.local) == true)
                   {
                        SortedList names = new SortedList();
                        bool isDatabaseCommand = true;
                        if (setNoCountOn)
                        {
                            ForEach(connection, Resources.SETNOCOUNT, replaceChar, isDatabaseCommand, names,  msgLevel);
                        }

                       // First see if there is a precommand to execute
                        if (!string.IsNullOrEmpty(precommand))
                        {
                            SendMessage(msgLevel, Resources.strExecutingCommand +precommand);
                            ForEach(connection, precommand, replaceChar, isDatabaseCommand, names, msgLevel);
                        }
                      SendMessage(msgLevel, Resources.strForEachDBSQLQuery);
                      using (SqlCommand cmd = new SqlCommand(Resources.strForEachDBSQLQuery, connection))
                      {
                          cmd.CommandType = System.Data.CommandType.Text;
                          SqlDataReader rdr = cmd.ExecuteReader();
                          while (rdr.Read())
                          {
                              string dbName = rdr.GetString(0);
                              names.Add(dbName, dbName);
                          }
                          rdr.Close();
                          SendMessage(msgLevel,Resources.found + names.Count.ToString(CultureInfo.CurrentCulture) + Resources.dbsToProcess);
                        }
                    
                        // Now do the work for each database found
                        ForEach(connection, command1, replaceChar, isDatabaseCommand, names, msgLevel);
                        if (!string.IsNullOrEmpty(command2))
                        {
                            SendMessage(msgLevel, Resources.strExecutingCommand + command2);
                            ForEach(connection, command2, replaceChar, isDatabaseCommand, names, msgLevel);
                        }
                        if (!string.IsNullOrEmpty(command3))
                        {
                            //AddNoCount(setNoCountOn, command3);
                            SendMessage(msgLevel, Resources.strExecutingCommand + command3);
                            ForEach(connection, command3, replaceChar, isDatabaseCommand, names, msgLevel);
                        }
                       // now if there is a post command, execute that
                        if (!string.IsNullOrEmpty(postcommand))
                        {
                            names.Clear();
                            ForEach(connection, postcommand, replaceChar, isDatabaseCommand, names, msgLevel);
                        }
                   }
               }
           }
           return (retValue);
       }

       /// <summary>
       /// This is the entry point for the SQL CLR stored procedure ExecuteForEachTable
       /// </summary>
       /// <param name="command1">This is the command to be iterated over for each database.
       /// calling with an empty command will present the user with detailed usage help
       /// </param>
       /// <param name="database">Do to the way that SQL CLR stored procedures work, the user must specify the database
       /// that will be used for this call
       /// </param>
       /// <param name="replaceChar">If the user cannot use the ? as the replacement character they may specific one here
       /// </param>
       /// <param name="command2">This is the command to be iterated over for each database.
       /// this command will be issued after iterating through all databases for the first command
       /// </param>
       /// <param name="command3">This is the command to be iterated over for each database.
       /// this command will be issued after iterating through all databases for the first two command
       /// </param>
       /// <param name="precommand">A statement to run once at the begining of the batch
       /// </param>
       /// <param name="postcommand">A statement to run once at the end of the batch</param>
       /// <param name="setNoCountOn">
       /// An option to turn NOCOUNT on or off
       /// </param>
       /// <param name="messageOptions">Determines the level of feedback to the user during execution</param>
       /// <returns>zero if successful, non-zero if unsuccessful</returns>
       [Microsoft.SqlServer.Server.SqlProcedure(Name = "ExecuteForEachTable")]
       public static void ExecuteForEachTable(
           string command1
           , string database
           , string replaceChar
           , string command2
           , string command3
           , string precommand
           , string postcommand
           , bool setNoCountOn = true
           , string messageOptions = "Errors"
           )
       {
           // Since this is a SQL CLR stored procedure we must use the context connection
           using (SqlConnection connection = new SqlConnection(Resources.contextConnectionString))
           {
               connection.Open();
               MessageLevels msgLevel = MessageLevel(messageOptions);
               if (!(msgLevel == MessageLevels.Invalid))
               {
                   SendMessage(msgLevel, Resources.validateInputParameters);
                   // Validate the parameters are valid. If not, notify the user of the syntax and exit
                   if (ValidateParameters(command1, database) == true)
                   {
                       // let's try to change to the requested database context
                       SendMessage(msgLevel, Resources.currentDatabase + connection.Database);
                       try
                       {
                           // We perform this in a try/catch block in case the user doesn't have rights to that
                           // database, or it does not exist
                           connection.ChangeDatabase(database);
                           // This flag is used to determine which option to take in ForEach method
                           // this must be true to issue without processing we will change this
                           // after we issue the precommand, if present
                           bool isDatabaseCommand = false;

                           // We use this to hold the names of the tables for the ForEach method
                           SortedList names = new SortedList();

                           SendMessage(msgLevel, Resources.contextChanged + database);
                           if (setNoCountOn)
                           {
                               ForEach(connection, Resources.SETNOCOUNT, replaceChar, isDatabaseCommand, names, msgLevel);
                           }
                           if (!string.IsNullOrEmpty(precommand))
                           {
                               SendMessage(msgLevel, Resources.strExecutingCommand + precommand);
                               ForEach(connection, precommand, replaceChar, isDatabaseCommand, names, msgLevel);
                           }
                           SendMessage(msgLevel, Resources.strForEachTableSQLQueryMessage);

                           // Get a list of user tables in the current database
                           // we use the using clause to automatically clean up our SqlCommand object
                           using (SqlCommand cmd = new SqlCommand(Resources.strForEachTableSQLQuery, connection))
                           {
                               //cmd.CommandType = System.Data.CommandType.Text;
                               // While we have rows, read them and save them to the names collection
                               SqlDataReader rdr = cmd.ExecuteReader();
                               while (rdr.Read())
                               {
                                   string tblName = rdr.GetString(0);
                                   names.Add(tblName, tblName);
                               }
                               rdr.Close();
                               SendMessage(msgLevel, Resources.found + names.Count.ToString(CultureInfo.CurrentCulture) + Resources.tablesToProcess);
                           }
                           // Now do the work for each table found
                           ForEach(connection, command1, replaceChar, isDatabaseCommand, names, msgLevel);
                           if (!string.IsNullOrEmpty(command2))
                           {
                               SendMessage(msgLevel, Resources.strExecutingCommand + command2);
                               ForEach(connection, command2, replaceChar, isDatabaseCommand, names, msgLevel);
                           }
                           if (!string.IsNullOrEmpty(command3))
                           {
                               //AddNoCount(setNoCountOn, command3);
                               SendMessage(msgLevel, Resources.strExecutingCommand + command3);
                               ForEach(connection, command3, replaceChar, isDatabaseCommand, names, msgLevel);
                           }
                           // now if there is a post command, execute that
                           if (!string.IsNullOrEmpty(postcommand))
                           {
                               names.Clear();
                               ForEach(connection, postcommand, replaceChar, isDatabaseCommand, names, msgLevel);
                           }
                       }
                       catch (SqlException e)
                       {
                           SqlContext.Pipe.Send(e.Source + Resources.colonSpace + e.Message);

                       }
                   }
               }
           }
       }
        /// <summary>
        /// This method validates the input parameters, since we allow default values to be pass by SQL Server
        /// </summary>
        /// <param name="command1">The command to execute</param>
        /// <param name="databaseContext">The database on which to execute.
        /// If equal to local (.) then this is for ForEachDB</param>
        /// <returns>was the validation successful</returns>
       private static bool ValidateParameters(string command1, string databaseContext)
       {
           bool valid = true;
           // If the command is empty - return help information to the user
           if (String.IsNullOrEmpty(command1))
           {
               // Execute the command and send the results directly to the client.
               SqlContext.Pipe.Send(Resources.commandEmpty);
               valid = false;
           }
           // If the databaseContext is empty - return help to the user based on who called us
           if (valid && String.IsNullOrEmpty(databaseContext))
           {
               SqlContext.Pipe.Send(Resources.databaseContextEmpty);
               valid = false;
           }
           return valid;
       }
        /// <summary>
        /// This helper method will perform the same role as the system stored procedure sp_MSForEachWorker
        /// </summary>
        /// <param name="connection">The context connection, since all methods are static</param>
        /// <param name="command">The command to execute</param>
        /// <param name="replacechar">The replacement character that will be substituted by each of the Names</param>
        /// <param name="isDatabaseCommand">Determines who called us, ForEachDB or ForEachTable</param>
        /// <param name="names">SortedList of names for use in iterating the commands</param>
        /// <param name="msgLevel">Determines the level of feedback to the user during execution</param>
        /// <returns></returns>
       private static void ForEach(SqlConnection connection, string command, string replacechar, bool isDatabaseCommand, SortedList names, MessageLevels msgLevel)
       {
           
           //pre or post command
           if (names.Count == 0)
           {
               using (SqlCommand cmd = new SqlCommand(command, connection))
               {
                   SqlContext.Pipe.ExecuteAndSend(cmd);
               }
           }
           command = SingleTickToDoubleTick(command);
           foreach (string name in names.Keys)
           {
               // perform replacement character substitution
               if(!HasReplacementChar(command,replacechar) && isDatabaseCommand)
               {
                   try
                   {
                       // We perform this in a try/catch block in case the user doesn't have rights to that
                       // database, or it does not exist
                       connection.ChangeDatabase(name);
                       SendMessage(msgLevel, Resources.contextChanged + name);
                   }
                   catch (SqlException e)
                   {
                       SqlContext.Pipe.Send(e.Source + Resources.colonSpace + e.Message);
                   }
                   
               }

               SendMessage(msgLevel, Resources.strDatabase + name);
               SendMessage(msgLevel, Resources.strBeforeSubstitution + command);
               string substitutedCommand = Replace(command, replacechar, name, msgLevel, isDatabaseCommand);
               SendMessage(msgLevel, Resources.strExecutingCommand + substitutedCommand);
               string sql = Resources.beginEXEC + substitutedCommand + Resources.endEXEC;
               SendMessage(msgLevel, "1:" + sql);
               // Execute and send the results back to the user
               // if this involves results sets they will appear as normal
               // if this involves a non-query then the Messages tab will receive the output
               using (SqlCommand cmd = new SqlCommand(sql, connection))
               {
                   SendMessage(msgLevel, Resources.strExecutingCommand + sql);
                    SqlContext.Pipe.ExecuteAndSend(cmd);
               }
           }
       }
       /// <summary>
       /// This is the heart of the work. the substituion of the ? with the name
       /// </summary>
       /// <param name="input">This is the string into which we will place the names</param>
       /// <param name="replacechar">this is the character that identifies where to substitute</param>
       /// <param name="name">this is the name which will replace the ?</param>
       /// <param name="msgLevel">Determines the level of feedback to the user during execution</param>
       /// <param name="db">We have to quote things differently if we are substituting database names</param>
       /// <returns>
       /// a string with the replacement char being substituted by the name presented.
       /// </returns>
       private static string Replace(string input, string replacechar, string name, MessageLevels msgLevel, bool db)
        {
            string retString =input;
            string nameSave = name;
            SendMessage(msgLevel, "2:" + input);
            int replaceCharIndex = input.IndexOf(replacechar);
            while (replaceCharIndex != -1)
            {
                int nameLength = name.Length;
                int tempIndex = 0;
                if (input.Substring(replaceCharIndex - 1, 1) == @"'")
                {
                    name = name.Replace("'", "''");
                    SendMessage(msgLevel, Resources.strFoundDoubleQuotedName);
                }
                else if (Regex.IsMatch(name, @"^\[.*\]\..*\]") )
                {
                    SendMessage(msgLevel, Resources.strOwnerQualified);
                    tempIndex = name.IndexOf("].[");
                    string nameTemp = name.Substring(1, tempIndex - 1);
                    string nameTemp2 = name.Substring(tempIndex + 3, nameLength - tempIndex - 4);
                    SendMessage(msgLevel, Resources.strNamePrefix + nameTemp);
                    SendMessage(msgLevel, Resources.strNameSuffix + nameTemp2);
                    nameTemp = nameTemp.Replace("]", "]]");
                    SendMessage(msgLevel, Resources.strNameSuffix + nameTemp2);
                    nameTemp2 = nameTemp2.Replace("]", "]]");
                    SendMessage(msgLevel, Resources.strNameSuffix + nameTemp2);
                    name = "[" + nameTemp + "].[" + nameTemp2 + "]";
                    SendMessage(msgLevel, "2:name " + name);

                    if (db == true)
                    {
                        //SendMessage(msgLevel,Resources.strAddingleadingQuotes + name);
                        name = "''" + name + "''";
                        SendMessage(msgLevel, Resources.strAddingTrailingQuotes +name);
                    }
                }
                else if (Regex.IsMatch(name, @"^\[.*\]"))
                {
                    string nameTemp = name.Substring(2, name.Length - 2);
                    nameTemp = nameTemp.Replace("]", "]]");
                    name = "[" + nameTemp + "]";
                    SendMessage(msgLevel, Resources.strBracketedTableName + name);
                }
                
                if (replaceCharIndex < input.Length)
                {
                    SendMessage(msgLevel, "2:" + name);
                    SendMessage(msgLevel, "2:" + replaceCharIndex.ToString());
                    SendMessage(msgLevel, "2:" + name.Length.ToString());
                    input = input.Remove(replaceCharIndex, 1);
                    if (db == true)
                    {
                        SendMessage(msgLevel, Resources.strWrappingDBNameInQuotes);
                        input = input.Insert(replaceCharIndex, "''" + name + "''");
                    }
                    else
                    {
                        SendMessage(msgLevel, Resources.strNotWrappingName);
                        input = input.Insert(replaceCharIndex, name);
                    }
                    retString = input;
                    SendMessage(msgLevel, "2:"+retString);
                }
                replaceCharIndex = retString.IndexOf(replacechar);
                SendMessage(msgLevel, Resources.strMultiCharReplacement + replaceCharIndex.ToString());
            }

            return retString;
        }
       /// <summary>
       /// This will allow us to send messages back to the user via SqlPipe
       /// </summary>
       /// <param name="msgLevel">Determines the level of feedback to the user during execution</param>
       /// <param name="message">The message to sent</param>
       /// <remarks>
       ///  for example
       ///  0 - only error messages
       ///  1 - progress messages
       ///  2 - all possible messages
       /// </remarks>
       private static void SendMessage(MessageLevels msgLevel, string message)
       {
           if (message.Substring(1, 1) == ":")
           {
               int messageLevel = Convert.ToInt32(message.Substring(0, 1));
               int targetLevel = Convert.ToInt32(msgLevel);

               string msg = message.Substring(2);
               if (messageLevel <= targetLevel)
               {
                   SqlContext.Pipe.Send(msg);
               }
           }
           else
           {
               SqlContext.Pipe.Send(message);
           }
       }
       private static bool HasReplacementChar(string input, string replacechar)
       {
           bool hasReplacementChar = true;
           int index = input.IndexOf(replacechar);
           if (index == -1)
           {
               hasReplacementChar = false;

           }
           return hasReplacementChar;
       }
       private static string SingleTickToDoubleTick(string input)
       {
           string newString ="";
           char[] seperators = new char[] { Resources.splitText[0] };
           string[] pieces = input.Split(seperators);
           foreach (string p in pieces)
           {
               newString += p + "''";
           }
           newString = newString.Substring(0, newString.Length - 2);
           return newString;
       }
       private static string AddNoCount(bool addNoCount, string command,MessageLevels msgLevel)
       {
           string added = (addNoCount) ? Resources.SETNOCOUNT + command : command;
           SendMessage(msgLevel, added);
           return added;
       }
       private static MessageLevels MessageLevel(string msgOptions)
       {
           MessageLevels level = MessageLevels.Errors;
           switch (msgOptions.ToUpper())
           {
               case "ERRORS":
                   level = MessageLevels.Errors;
                   break;
               case "PROGRESS":
                   level = MessageLevels.Progress;
                   break;
               case "ALL":
                   level = MessageLevels.All;
                   break;
               default:
                   SqlContext.Pipe.Send(Resources.MessageLevelMessage);
                   level = MessageLevels.Invalid;
                   break;
           }
           return level;
       }
    };
}
